package com.loveyukari.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.loveyukari.dao.YukariMusciDao;
import com.loveyukari.model.Music;
import com.loveyukari.model.SongSheet;

import net.sf.json.JSONObject;

/**
*
*yangshaohua
*2017年3月3日下午4:19:08
**/

@Repository
public class JdbcYukariMusicDao extends BaseDao implements YukariMusciDao{
	
	class YukariMusicRowMapper implements RowMapper<Music>{

		public Music mapRow(ResultSet rs, int rowNum) throws SQLException {
			Music music = new Music();
			music.setId(rs.getInt("id"));
			music.setName(rs.getString("name"));
			music.setDescription(rs.getString("description"));
			music.setNameCn(rs.getString("name_cn"));
			music.setType(rs.getString("type"));
			music.setLastSingTime(rs.getDate("last_sing_date"));
			music.setStatus(rs.getInt("status"));
			music.setContent(rs.getString("content"));
			return music;
		}
		
	}

	public List<Music> selectListByType(String type) {
		String sql = "select * from yukari_music where type = ? order by last_sing_date";
		List<Music> list = this.getJdbcTemplate().query(sql, new YukariMusicRowMapper(),type);
		return list;
	}

	public List<Music> selectListByAll() {
		String sql = "select * from yukari_music order by last_sing_date";
		List<Music> list = this.getJdbcTemplate().query(sql, new YukariMusicRowMapper());
		return list;
	}

	public List<Music> selectListBySearch(String type,String query,String order) {
		String search = "%"+query+"%";
		StringBuilder sb = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sb.append("select * from yukari_music where ");
		if (StringUtils.isNotEmpty(type)){
			sb.append("type=? and ");
			params.add(type);
		}
		sb.append("(name like ? or description like ? or name_cn like ?) ");
		params.add(search);
		params.add(search);
		params.add(search);
		if (StringUtils.isNotBlank(order)){
			sb.append("order by ").append(order);
		}
		String sql = sb.toString();
		List<Music> list = this.getJdbcTemplate().query(sql, new YukariMusicRowMapper(),params.toArray());
		return list;
	}

	public Music select(int id) {
		String sql = "select * from yukari_music where id = ?";
		List<Music> list = this.getJdbcTemplate().query(sql, new YukariMusicRowMapper(),id);
		if (list.isEmpty()){
			return null;
		}
		return list.get(0);
	}

	public List<Music> selectListBySize(String query, int size) {
		query = "%"+query+"%";
		String sql = "select * from yukari_music where (name like ? or description like ? or name_cn like ?) limit ?";
		List<Music> list = this.getJdbcTemplate().query(sql,new YukariMusicRowMapper(),query,query,query,size);
		return list;
	}

	public void insertSongSheet(SongSheet songSheet) {
		String sql = "insert into yuakri_song_sheet (music_id,sing_date,rank) values (?,?,?)";
		this.getJdbcTemplate().update(sql,songSheet.getMusicId(),songSheet.getSingDate(),songSheet.getRank());
	}
	
	
	public void insertMusic(Music music){
		String sql = "insert into yukari_music (name,description,type,name_cn,content) values(?,?,?,?,?)";
		this.getJdbcTemplate().update(sql,music.getName(),music.getDescription(),music.getType(),music.getNameCn(),music.getContent());
	}
	public void updateMusic(Music music){
		String sql = "update yukari_music set name=?,description=?,type=?,name_cn=?,content=?,last_sing_date=?,status=? where id = ?";
		this.getJdbcTemplate().update(sql,music.getName(),music.getDescription(),music.getType(),music.getNameCn(),music.getContent(),music.getLastSingTime(),music.getStatus(),music.getId());
		
	}

	public List<JSONObject> selectSongSheetGroupDate() {
		String sql = "select sing_date,count(*) as c from yuakri_song_sheet group by sing_date order by sing_date desc";
		final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		final SimpleDateFormat weekSdf = new SimpleDateFormat("EEEE");
		List<JSONObject> list = this.getJdbcTemplate().query(sql, new RowMapper<JSONObject>(){

			public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
				JSONObject result = new JSONObject();
				Date date = rs.getTimestamp("sing_date");
				result.put("singDate", sdf.format(date));
				result.put("week", weekSdf.format(date));
				result.put("count", rs.getInt("c"));
				return result;
			}
		});
		return list;
	}

	public List<Integer> selectMusicIdbySheet(Date date) {
		String sql = "select music_id from yuakri_song_sheet where sing_date = ? order by rank";
		List<Integer> list = this.getJdbcTemplate().queryForList(sql,Integer.class,date);
		return list;
	}

}
